/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ttt.nor2.alarm;

import com.ttt.nor2.config.CfgAlarm;
import com.ttt.nor2.db.*;
import com.ttt.nor2.util.ExceptionUtil;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Hashtable;
import org.apache.log4j.Logger;

/**
 *
 * @author anuphabm
 */
public class ALarmSendSMS {

    private Logger logger = Logger.getLogger(ALarmSendSMS.class);
    private ALarmFromExchange alarm;
    private HashMap hmEmp;
    private CfgAlarm cfg;
    private int row = 1;

    public HashMap getHmEmp() {
        return hmEmp;
    }

    public ALarmSendSMS(ALarmFromExchange alarm, CfgAlarm cfg) {
        this.alarm = alarm;
        this.cfg = cfg;
        hmEmp = new HashMap();
    }

    public void prcess() {
        // set status  = P
        setStatus("P");
        if (isCheckNumALarm()) { // if has fault = true
            // get pos noc
            findNOC();
            findEmpTakeExchange();// return hashmap EmpTakechange
            SendSMS2Webservice send = new SendSMS2Webservice(hmEmp, cfg);
            if (send.loopSend()) {
                moveAlarm2hist("alarm_hist_enable");
            } else {
                setStatus("E");
            }
        } else {
            // move data to alarm history disable
            moveAlarm2hist("alarm_hist_disable");
        }
    }

    private void findEmpTakeExchange() {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT ec.location_id, ec.exch_name, m.m_id, m.s_id, m.e_id ";
            sql += "FROM equ_name AS eq Inner Join exchange AS ec ON eq.location_id = ec.location_id ";
            sql += "Inner Join mainternace AS m ON ec.location_id = m.location_id WHERE ";
            sql += "eq.ncen_code = ? AND eq.ur_code = ?";
            logger.debug("sql[" + sql + "][" + alarm.getNcenCode() + "][" + alarm.getUrCode() + "]");
            pstmt = cfg.getConn().prepareStatement(sql);
            pstmt.setInt(1, alarm.getNcenCode());
            pstmt.setString(2, alarm.getUrCode());
            rs = pstmt.executeQuery();

            while (rs.next()) {
                EmpTakeExchange emp = new EmpTakeExchange();
                int empid = rs.getInt("e_id");
                emp.setEmpOperator(empid);
                emp.setExchangeName(rs.getString("exch_name"));
                emp.setLocation(rs.getString("location_id"));
                emp.setAlarm(alarm);

                if (isEmpSpacial(rs.getInt("e_id"))) {
                    int mid = rs.getInt("m_id");
                    if (findEmpSpacial(mid)) {
                        emp.setEmpManager(mid);
                        //alarm.setTransid(alarm.getTransid() + "m");
                    }
                    int sid = rs.getInt("s_id");
                    if (findEmpSpacial(sid)) {
                        emp.setEmpManager(sid);
                        //alarm.setTransid(alarm.getTransid() + "s");
                    }
                    if (findEmpSpacial(empid)) {
                        hmEmp.put(Integer.toString(row), emp);
                        //alarm.setTransid(alarm.getTransid() + "o");
                        row++;
                    }
                } else {
                    emp.setEmpManager(rs.getInt("m_id"));
                    emp.setEmpSupervisor(rs.getInt("s_id"));
                    hmEmp.put(Integer.toString(row), emp);
                    row++;
                }

            }
            rs.close();
            pstmt.close();
            rs = null;
            pstmt = null;
        } catch (SQLException e) {
            logger.error("Exception error" + ExceptionUtil.getStackTrace(e));
            System.exit(-1);
        }

    }

    private boolean isCheckNumALarm() {
        boolean flagChk = false;
        Hashtable ht = cfg.getHashtable();
        logger.debug(alarm.getTransid() + " size is ht[" + ht.size() + "]");
        logger.debug(alarm.getTransid() + " alarm_num[" + alarm.getAlarmNum() + "]");
        String numAlarm = Integer.toString(alarm.getAlarmNum());
        ALarmDescription alarm_num = (ALarmDescription) ht.get(numAlarm);

        if (alarm_num == null) {
            flagChk = false;
        } else {
            alarm.setAlarm_desc(alarm_num.getAlarm_desc_en());
            flagChk = true;
        }
        logger.info(alarm.getTransid() + " rtn check num alarm[" + flagChk + "]");
        return flagChk;
    }

    private void setStatus(String status) {// set status alarm_exchange = P
        String sql = "update alarm_exchange set status=? where transid = ?";
        try {
            PreparedStatement pstmt = cfg.getConn().prepareStatement(sql);
            pstmt.setString(1, status);
            pstmt.setString(2, alarm.getTransid());
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;
            logger.info("update transid " + alarm.getTransid() + " status = " + status);
        } catch (SQLException e) {
            logger.error("Exception update alarm_exchage set status=P error" + ExceptionUtil.getStackTrace(e));
            System.exit(-1);
        }

    }

    private void moveAlarm2hist(String tableName) { // disable move to alarm_hist_disable , enable move to alarm_hist_enable
        try {

            String sql = "insert into " + tableName + "(transid, class_id, msg_format, alarm_num, datetime_alarm, ";
            sql += "alarm_id, type, urgency, event, ncen_code, exchange_name, ur_code, equ_name, equ_addr, alarm_text, ";
            sql += "status, ondate, stamptime)select transid, class_id, msg_format, alarm_num, datetime_alarm, ";
            sql += "alarm_id, type, urgency, event, ncen_code, exchange_name, ur_code, equ_name, equ_addr, alarm_text, ";
            sql += "status, ondate, stamptime from alarm_exchange where transid =?";
            PreparedStatement pstmt = cfg.getConn().prepareStatement(sql);
            pstmt.setString(1, alarm.getTransid());
            pstmt.executeUpdate();
            pstmt.close();
            logger.debug("Insert into alarm_hist_disable " + alarm.getTransid());
            //delete alarm_hist_disable
            sql = "delete from alarm_exchange where transid = ?";
            pstmt = cfg.getConn().prepareStatement(sql);
            pstmt.setString(1, alarm.getTransid());
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;
            logger.info("delete alarm_exchange " + alarm.getTransid());

        } catch (SQLException e) {
            logger.error("Exception move & del error " + ExceptionUtil.getStackTrace(e));
            System.exit(-1);
        }
    }

    private void findNOC() {
        try {
            String sql = "SELECT ec.location_id, ec.exch_name FROM equ_name AS eq  " +
                    "Inner Join exchange AS ec ON eq.location_id = ec.location_id  " +
                    "WHERE eq.ncen_code = ? AND eq.ur_code = ?";

            logger.debug("sql[" + sql + "][" + alarm.getNcenCode() + "][" + alarm.getUrCode() + "]");
            PreparedStatement pstmt = cfg.getConn().prepareStatement(sql);
            pstmt.setInt(1, alarm.getNcenCode());
            pstmt.setString(2, alarm.getUrCode());
            ResultSet rs = pstmt.executeQuery();

            String location = "";
            String exchName = "";
            if (rs.next()) {
                location = rs.getString("location_id");
                exchName = rs.getString("exch_name");
            }
            logger.debug(alarm.getTransid() + " location[" + location + "]");
            logger.debug(alarm.getTransid() + " location[" + exchName + "]");
            rs.close();
            pstmt.close();
            rs = null;
            pstmt = null;

            sql = "select emp_id from employee where pos_id='NOC'";
            pstmt = cfg.getConn().prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                EmpTakeExchange emp = new EmpTakeExchange();
                emp.setEmpManager(rs.getInt("emp_id"));
                emp.setExchangeName(exchName);
                emp.setLocation(location);
                emp.setAlarm(alarm);
                hmEmp.put(Integer.toString(row), emp);
                row++;
            }
            rs.close();
            pstmt.close();
            rs = null;
            pstmt = null;

        } catch (SQLException e) {
            logger.error("Exception error" + ExceptionUtil.getStackTrace(e));
            System.exit(-1);
        }

    }

    private boolean findEmpSpacial(int empid) {
        boolean flagFindEmp = false;
        String sql = "SELECT ras.* FROM req_alarm_spacial AS ras Inner Join employee AS e ON ras.emp_id = e.emp_id " +
                "WHERE e.flag_req_spacial =  'Y' AND e.emp_id =  ? AND ras.status_alarm =  'Y' " +
                "and ras.alarm_num = ? and ras.day_alarm = DAYOFWEEK(now()) " +
                "and  (date_format(now(),'%H%i') > ras.time_alarm_b  or date_format(now(),'%H%i') < ras.time_alarm_e) ";
        try {
            PreparedStatement pstmt = cfg.getConn().prepareStatement(sql);
            pstmt.setInt(1, empid);
            pstmt.setInt(2, alarm.getAlarmNum());
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                flagFindEmp = true;
            }
            rs.close();
            pstmt.close();
            rs = null;
            pstmt = null;
        } catch (SQLException e) {
            logger.error("Exception find employee spacial error " + ExceptionUtil.getStackTrace(e));
            System.exit(-1);
        }
        logger.info(alarm.getTransid() + " rtn find employee spacial[" + flagFindEmp + "]");
        return flagFindEmp;

    }

    private boolean isEmpSpacial(int empid) {
        boolean flagSpacial = false;
        String sql = "select * from employee where emp_id=? and flag_req_spacial ='Y'";
        try {
            PreparedStatement pstmt = cfg.getConn().prepareStatement(sql);
            pstmt.setInt(1, empid);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                flagSpacial = true;
            }
            rs.close();
            pstmt.close();
            rs = null;
            pstmt = null;
        } catch (SQLException e) {
            logger.error("Exception query flag request spacail error " + ExceptionUtil.getStackTrace(e));
            System.exit(-1);
        }
        logger.info(alarm.getTransid() + " rtn get employee spacial[" + flagSpacial + "]");
        return flagSpacial;
    }
}
